package com.myidis.servlet;

import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.ArrayList;

import com.auxiliary.upload_download.Download;
import com.auxiliary.upload_download.upload;
import com.myidis.entity.*;
import com.myidis.mapper.QuotaDataItemMapper;
import com.myidis.mapper.QuotaMapper;
import com.myidis.mapper.QuotaQueryMapper;
import com.myidis.mapper.QuotaTypeMapper;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.myidis.request.SearchReq;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@Service
public class QuotaQueryServlet {
	@Autowired
	private QuotaQueryMapper quotaQueryMapper;
    @Autowired
    private QuotaMapper quotaMapper;
    @Autowired
    private QuotaDataItemMapper quotaDataItemMapper;
    @Autowired
    private QuotaTypeMapper quotaTypeMapper;

    public ArrayList<Quota> allName(){
        return quotaMapper.allName();
    }

    public ArrayList<QuotaList> search1(SearchReq sea){
        String name = "%" +sea.getKeyWord2()+"%";
        return quotaQueryMapper.getByName(sea.getKeyWord1(),name);
    }

    public ArrayList<QuotaList> search2(SearchReq sea){
        return quotaQueryMapper.getDataItems(sea);
    }

    public int change(QuotaData cha) {
		return quotaQueryMapper.update(cha);
	}

	public ArrayList<QuotaData> ret(SearchReq sea) {
		return quotaQueryMapper.getOne(sea);
	}


    public String upload(MultipartFile file, Quota quota) throws Exception {
        //1:创建workbook
        Workbook workbook=Workbook.getWorkbook(file.getInputStream());
        //2:获取第一个工作表sheet
        Sheet sheet=workbook.getSheet(0);
        //3:获取数据并创建一个对象，用对象创建sql语句注入数据库
        for(int i = 1; i < sheet.getRows(); i++){
            QuotaData as = new QuotaData();
            as.setTime(sheet.getCell(0,i).getContents());
            as.setQuota(Integer.parseInt(sheet.getCell(1,i).getContents()));
            as.setDataItem(Integer.parseInt(sheet.getCell(2,i).getContents()));
            as.setFrequency(Integer.parseInt(sheet.getCell(3,i).getContents()));
            as.setValue(Double.parseDouble(sheet.getCell(4,i).getContents()));
            as.setUnit(sheet.getCell(5,i).getContents());
            quotaQueryMapper.insert(as);
        }
        //最后一步：关闭资源
        workbook.close();

        quotaMapper.insert(quota);
        ArrayList<QuotaType> quotaTypes = quotaTypeMapper.all();
        for (int i = 0; i <= quotaTypes.size(); i++){
            if(quotaTypes.get(i).getName().equals(quota.getTypeName())) break;
            else quotaTypeMapper.sinsert(quota.getCode(), quota.getTypeName(), quota.getTypeName());
        }
        ArrayList<QuotaDataItem> quotaDataItem = quotaDataItemMapper.all();
        for (int i = 0; i <= quotaDataItem.size(); i++){
            if(quotaDataItem.get(i).getName().equals(quota.getDataitem())) break;
            else quotaDataItemMapper.sinsert(quota.getDataitem(),quota.getDataitem());
        }
        return "yes";
    }

    public int download(SearchReq sea, HttpServletResponse response) throws SQLException, IOException, WriteException {
        File file = Download.download(sea);
        if (file.exists()) {
            response.setContentType("application/force-download");// 设置强制下载不打开
            response.addHeader("Content-Disposition",
                    "attachment;fileName= download.xls");// 设置文件名
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {
                fis = new FileInputStream(file);
                bis = new BufferedInputStream(fis);
                OutputStream os = response.getOutputStream();
                int i = bis.read(buffer);
                while (i != -1) {
                    os.write(buffer, 0, i);
                    i = bis.read(buffer);
                }
                System.out.println("success");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        return 1;
    }

    public ArrayList<QuotaData> bytime(SearchReq sea) {
        return quotaQueryMapper.searchByTime(sea);
    }

    public String supload(MultipartFile file) {
        //1:创建workbook
        Workbook workbook= null;
        try {
            workbook = Workbook.getWorkbook(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        //2:获取第一个工作表sheet
        Sheet sheet=workbook.getSheet(0);
        //3:获取数据并创建一个对象，用对象创建sql语句注入数据库
        for(int i = 1; i < sheet.getRows(); i++){
            QuotaData as = new QuotaData();
            as.setTime(sheet.getCell(0,i).getContents());
            as.setQuota(Integer.parseInt(sheet.getCell(1,i).getContents()));
            as.setDataItem(Integer.parseInt(sheet.getCell(2,i).getContents()));
            as.setFrequency(Integer.parseInt(sheet.getCell(3,i).getContents()));
            as.setValue(Double.parseDouble(sheet.getCell(4,i).getContents()));
            as.setUnit(sheet.getCell(5,i).getContents());
            quotaQueryMapper.insert(as);
        }
        //最后一步：关闭资源
        workbook.close();


        return "yes";
    }
}
